package com.wms.service;

import com.mes.entity.User;
import com.wms.entity.Inventory;
import com.yy.base.common.dao.Dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

/**
 * 盘点
 */
@Service
public class InventoryService {
	@Autowired
	private Dao dao;

	public Map<String, Object> getInventoryPlan(String locatorCode) {
		String sql = "select wms_inventory_plan_ID id, p.M_WAREHOUSE_ID pwid, w.m_warehouse_id warehouseId, p.STATUS " +
				"  from m_warehouse w, wms_inventory_plan p " +
				" where w.m_warehouse_id = p.m_warehouse_id" +
				"	and now() BETWEEN Begin_Date and End_Date + interval '1 D' " +
				"   and w.value = ?" +
				" order by (case when p.STATUS = 'CREATE' then 0 else 1 end), p.created desc, end_date desc";
		return dao.queryUnique(sql, locatorCode);
	}

	/**
	 * 获取货位物料数量
	 * @param locatorCode
	 * @return
	 */
	public List<Map<String, Object>> getLocatorProductQty(String locatorCode) {
		String sql = "select sb.m_product_id, io.m_warehouse_id, p.name,  p.value val, sum(io.qty) qty     " +
				"    from   m_warehouse w, m_product p, Wms_Storage_In_Out io, wms_storage_batch sb      " +
				"   where  w.m_warehouse_id = io.m_warehouse_id    " +
				"     and io.status <> 'CANCEL'    " +
				"     and w.value = ?" +
				"     and sb.wms_storage_batch_id = io.wms_storage_batch_id  " +
				"     and sb.m_product_id = p.m_product_id  " +
				"   group by sb.m_product_id, io.m_warehouse_id, p.name,  p.value     " +
				"  having sum(io.qty) > 0 ;";
		return dao.query(sql, locatorCode);
	}

	/**
	 * 添加盘点信息
	 * @param inventory
	 * @return
	 */
	public int addInventory(Inventory inventory , User user) {
		String sql = "select * from Wms_Inventory where Wms_Inventory_Plan_ID = ? and M_Warehouse_ID = ? and m_product_ID=? ";
		List<Map<String, Object>> list = dao.query(sql, inventory.getInventoryPlanId(), inventory.getWarehouseId(), inventory.getProductId());
		if (list.isEmpty()) {
			sql = "insert into Wms_Inventory(Wms_Inventory_ID, M_Warehouse_ID, M_Locator_ID, m_product_ID, " +
					"Qty, Qty_Ok, Wms_Inventory_Plan_ID,c_uom_id,emes_vendor_id,AD_CLIENT_ID, AD_ORG_ID, CREATEDBY, UPDATEDBY ) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
			return dao.executeSql(sql, inventory.getId(),
					inventory.getWarehouseId(),
					inventory.getLocatorId(),
					inventory.getProductId(), inventory.getQty(),
					inventory.getQtyOk(), inventory.getInventoryPlanId(), inventory.getUomId(), inventory.getEmesVendorId(),
					user.getClientId(), user.getOrgId(), user.getId(), user.getId());
		} else {
			sql = "update Wms_Inventory set qty = ?, Qty_Ok = ? where Wms_Inventory_Plan_ID=? and M_Warehouse_ID =? and m_product_ID=?";
			return dao.executeSql(sql, inventory.getQty(), inventory.getQtyOk(), inventory.getInventoryPlanId(),
					inventory.getWarehouseId(), inventory.getProductId());
		}
	}
}